package cn.osworks.aos.builder.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.commons.lang3.StringUtils;

import cn.osworks.aos.core.asset.AOSUtils;
import cn.osworks.aos.core.typewrap.Dto;
import cn.osworks.aos.core.typewrap.Dtos;

public class DbTableInfo {

	public DbTableInfo() {
		// TODO Auto-generated constructor stub
	}
	
	public ArrayList<HashMap<String, String>>  getTabelInfoByTableData(Connection conn,String table){
		String sql = "select * from sap_conpool  limit 1";
		PreparedStatement stmt;
		Statement statement = null;
		//statement = conn.createStatement();
		ArrayList<HashMap<String, String>> tableInfoList = new ArrayList<HashMap<String, String>>();
		try {
			stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData data = rs.getMetaData();
			while (rs.next()) {
				for (int i = 1; i <= data.getColumnCount(); i++) {
					// 获得所有列的数目及实际列数
					int columnCount = data.getColumnCount();
					// 获得指定列的列名
					String columnName = data.getColumnName(i);
					// 获得指定列的列值
					String columnValue = rs.getString(i);
					// 获得指定列的数据类型
					int columnType = data.getColumnType(i);
					// 获得指定列的数据类型名
					String columnTypeName = data.getColumnTypeName(i);
					// 所在的Catalog名字
					String catalogName = data.getCatalogName(i);
					// 对应数据类型的类
					String columnClassName = data.getColumnClassName(i);
					// 在数据库中类型的最大字符个数
					int columnDisplaySize = data.getColumnDisplaySize(i);
					// 默认的列的标题
					String columnLabel = data.getColumnLabel(i);
					// 获得列的模式
					String schemaName = data.getSchemaName(i);
					// 某列类型的精确度(类型的长度)
					int precision = data.getPrecision(i);
					// 小数点后的位数
					int scale = data.getScale(i);
					// 获取某列对应的表名
					String tableName = data.getTableName(i);
					// 是否自动递增
					boolean isAutoInctement = data.isAutoIncrement(i);
					// 在数据库中是否为货币型
					boolean isCurrency = data.isCurrency(i);
					// 是否为空
					int isNullable = data.isNullable(i);
					// 是否为只读
					boolean isReadOnly = data.isReadOnly(i);
					// 能否出现在where中
					boolean isSearchable = data.isSearchable(i);
					
				 
					HashMap<String, String> tableInfo = new HashMap<String, String>();
					tableInfo.put("table_name", tableName);
					tableInfo.put("column_name", columnName);
					tableInfo.put("data_type", columnTypeName);
					tableInfo.put("is_nullable", "NO" );
					tableInfo.put("column_key", "");
					tableInfo.put("ordinal_position","0");
					tableInfo.put("column_comment", columnName);
					tableInfo.put("character_maximum_length", "100");
					tableInfo.put("display_width", "100");
					tableInfo.put("display_web", "Y");
					tableInfo.put("searchlike", "LIKE");
					tableInfo.put("display_combobox", "");
					tableInfo.put("orderby", "");
					tableInfo.put("defaultvalue","");
					tableInfo.put("web_search","");
					tableInfo.put("enable_bulider","Y");
					tableInfo.put("enable_list","Y");
					tableInfo.put("sql_option","R");
					tableInfo.put("web_readonly","false");
					tableInfoList.add(tableInfo);
					System.out.println("获得列" + i + "的字段名称:" + columnName);
					System.out.println("获得列" + i + "的字段值:" + columnValue);
					System.out.println("获得列" + i + "的类型,返回SqlType中的编号:" + columnType);
					System.out.println("获得列" + i + "的数据类型名:" + columnTypeName);
					System.out.println("获得列" + i + "所在的Catalog名字:" + catalogName);
					System.out.println("获得列" + i + "对应数据类型的类:" + columnClassName);
					System.out.println("获得列" + i + "在数据库中类型的最大字符个数:" + columnDisplaySize);
					System.out.println("获得列" + i + "的默认的列的标题:" + columnLabel);
					System.out.println("获得列" + i + "的模式:" + schemaName);
					System.out.println("获得列" + i + "类型的精确度(类型的长度):" + precision);
					System.out.println("获得列" + i + "小数点后的位数:" + scale);
					System.out.println("获得列" + i + "对应的表名:" + tableName);
					System.out.println("获得列" + i + "是否自动递增:" + isAutoInctement);
					System.out.println("获得列" + i + "在数据库中是否为货币型:" + isCurrency);
					System.out.println("获得列" + i + "是否为空:" + isNullable);
					System.out.println("获得列" + i + "是否为只读:" + isReadOnly);
					System.out.println("获得列" + i + "能否出现在where中:" + isSearchable);
					
				}
			}
		} catch (SQLException e) {
			System.out.println("数据库连接失败"+e.toString());
		}
		 return  tableInfoList;
	
	}
	public ArrayList<Dto> getTabelInfo(Connection conn,Dto infoMap){
		ArrayList<Dto> tableInfoList = new ArrayList<Dto>();
		try { 
			PreparedStatement stmt;
			String sql="select * from  information_schema.COLUMNS  where   TABLE_SCHEMA='"+infoMap.get("db_name_")+"'  and  table_name='"+infoMap.get("table_name_")+"' order by ORDINAL_POSITION ";
			stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				Dto tableInfo = Dtos.newDto();
				String column_name= rs.getString("column_name").toLowerCase();
				tableInfo.put("table_name_", rs.getString("table_name").toLowerCase());
				tableInfo.put("column_name_", rs.getString("column_name").toLowerCase());
				String data_type=rs.getString("data_type").toLowerCase();
				tableInfo.put("data_type_", rs.getString("data_type"));
				String is_nullable=rs.getString("is_nullable");
				is_nullable = AOSUtils.isEmpty(is_nullable)?"":is_nullable;
				is_nullable = is_nullable.toUpperCase().equals("YES")?"1":"0";
				tableInfo.put("is_nullable_", is_nullable);
				String key=rs.getString("column_key");
				key =  AOSUtils.isEmpty(key)?"0":"1";
				tableInfo.put("column_key_", key);
				tableInfo.put("fieldcheckexist_", "");
				tableInfo.put("ordinal_position_", rs.getString("ordinal_position"));
				tableInfo.put("column_comment_", rs.getString("column_comment"));
				String character_maximum_length=rs.getString("character_maximum_length");
				character_maximum_length = AOSUtils.isEmpty(character_maximum_length)?"100":character_maximum_length;
				tableInfo.put("character_maximum_length_",character_maximum_length);
				int max_ = Integer.parseInt(character_maximum_length)>100?Integer.parseInt(character_maximum_length):100; //如果宽度小100 显示 宽度默认100	 
				String format="";
				if (data_type.equals("datetime")||data_type.equals("timestamp")||data_type.equals("date")){
					 format="Y-m-d 00:00:00";
					 max_=160;
				}
				tableInfo.put("display_width_", max_);
				tableInfo.put("display_web_", column_name.toLowerCase().equals("id_")?"0":"1");  //如果是id列自动隐藏
				tableInfo.put("orderby_", "");
				tableInfo.put("defaultvalue_","");
				tableInfo.put("web_search_","0");
				tableInfo.put("enable_list_","1");
				tableInfo.put("sql_option_","C,U,R,D");
				tableInfo.put("web_readonly_","0");
				tableInfo.put("dicfield_","");
				
				tableInfo.put("format_",format);
				tableInfo.put("numeric_precision_",rs.getString("numeric_precision"));
				tableInfo.put("numeric_scale_",rs.getString("numeric_scale"));
				tableInfoList.add(tableInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	    return  tableInfoList;	
	}
	
 
	
	public ArrayList<Dto> getTabelInfoByBuilder_code(Connection conn,Dto infoMap){
		ArrayList<Dto> tableInfoList = new ArrayList<Dto>();
		try {
			PreparedStatement stmt;
			String sql="select * from builder_code  where table_name_='"+infoMap.get("table_name")+"' order by ORDINAL_POSITION_ ";
			stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				Dto tableInfo = Dtos.newDto();
				tableInfo.put("table_name_", rs.getString("table_name_").toLowerCase());
				String column_name_ = rs.getString("column_name_").toLowerCase();
				tableInfo.put("column_name_", column_name_);
				column_name_ = StringUtils.lowerCase(column_name_);
				tableInfo.put("column_name_upname", StringUtils.capitalize(column_name_));
				tableInfo.put("data_type_", rs.getString("data_type_"));
				tableInfo.put("is_nullable_", rs.getString("is_nullable_"));
				tableInfo.put("column_key_", rs.getString("column_key_"));
				tableInfo.put("ordinal_position_", rs.getString("ordinal_position_"));
				tableInfo.put("column_comment_", rs.getString("column_comment_"));
				tableInfo.put("character_maximum_length_", rs.getString("character_maximum_length_"));
				tableInfo.put("display_width_", rs.getString("display_width_"));
				tableInfo.put("display_web_", rs.getString("display_web_"));
				tableInfo.put("searchlike_", rs.getString("searchlike_"));
				tableInfo.put("orderby_",rs.getString("orderby_"));
				tableInfo.put("defaultvalue_",rs.getString("defaultvalue_"));
				tableInfo.put("web_search_",rs.getString("web_search_"));
				tableInfo.put("enable_list_",rs.getString("enable_list_"));
				tableInfo.put("web_readonly_",rs.getString("web_readonly_"));
				tableInfo.put("dicfield_",rs.getString("dicfield_"));
				tableInfo.put("id_",rs.getString("id_"));
				tableInfo.put("fieldcheckexist_",rs.getString("fieldcheckexist_"));
				tableInfo.put("builder_enable_", rs.getString("builder_enable_"));
				tableInfo.put("osdefault_", rs.getString("osdefault_"));
				tableInfo.put("format_",rs.getString("format_"));
				tableInfo.put("numeric_precision_",rs.getString("numeric_precision_"));
				tableInfo.put("numeric_scale_",rs.getString("numeric_scale_"));
				tableInfo.put("character_minimum_length_",rs.getString("character_minimum_length_"));
				tableInfo.put("max_",rs.getString("max_"));
				tableInfo.put("min_",rs.getString("min_"));
				tableInfoList.add(tableInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	    return  tableInfoList;	
	}
	
	public Dto getTabelInfoByComment(Connection conn,Dto infoMap){
		Dto tableInfo = Dtos.newDto();
		try {
			PreparedStatement stmt;
			String sql="select  TABLE_COMMENT from  information_schema.`TABLES`  where  TABLE_NAME='"+infoMap.get("table_name_")+"'";
			stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				tableInfo.put("table_comment", rs.getString("table_comment")); 
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	    return  tableInfo;	
	}
	
	public Dto getTabelInfoByBuilder_Table(Connection conn,Dto infoMap){
		Dto tableInfo = Dtos.newDto();
		try {
			PreparedStatement stmt;
			String sql="select * from builder_table  where table_name_='"+infoMap.get("table_name")+"' ";
			stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				tableInfo.put("table_name_", rs.getString("table_name_"));
				tableInfo.put("table_des_", rs.getString("table_des_"));
				tableInfo.put("db_name_", rs.getString("db_name_"));
				tableInfo.put("option_c", rs.getString("option_c"));
				tableInfo.put("option_d_", rs.getString("option_d_"));
				tableInfo.put("option_s_", rs.getString("option_s_"));
				tableInfo.put("option_u_", rs.getString("option_u_"));
				tableInfo.put("option_export_", rs.getString("option_export_"));
				tableInfo.put("sql_", rs.getString("sql_"));	 
				tableInfo.put("width_", rs.getInt("width_"));
				tableInfo.put("height_", rs.getInt("height_"));
				tableInfo.put("daopackage_", rs.getString("daopackage_"));	
				tableInfo.put("servicepackage_", rs.getString("servicepackage_"));
				tableInfo.put("controllername_", rs.getString("controllername_"));
				tableInfo.put("controllerpaceage_", rs.getString("controllerpaceage_"));
				tableInfo.put("sqlmapper_", rs.getString("sqlmapper_"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	    return  tableInfo;	
	}

}
